<?php
/**	
 * @author lxrmido@lxrmido.com
 */
class DB{

    public static $connection = null;
	
    
    /**
     * 插入数据条目
     * $map = array(
     *     'key1' => 'value1',
     *     'key2' => 'value2',
     *     ......
     * )
     * $table为表名
     */
    public static function insert($map, $table){
        return self::query(self::group_up_insert_sql($map, $table));
    }
    /**
     * 更新数据条目
     * $map = array(
     *     'key1' => 'value1',
     *     'key2' => 'value2',
     *     ......
     * )
     * $table为表名
     * $condition为WHERE后接的SQL条件语句，如"`number` > 100 AND `age` < 20"
     */
    public static function update($map, $table, $condition = 1){
        return self::query(self::group_up_update_sql($map, $table, $condition));
    }
    
	/**
	 * 查询结果，得到所有结果组成的数组，如
	 * all("SELECT * FROM `user`");将获得诸如：
	 * [
	 * 		{
	 * 			name : 'Bill',
	 * 			age  : 30
	 * 		},
	 * 		{
	 * 			name : 'Hilt',
	 * 			age  : 29
	 * 		}
	 * ]
	 * @param  String $sql SQL语句
	 * @return array()
	 */
	public static function all($sql){
		return self::fetch_all(self::query($sql));
	}
	/**	
	 * 查询结果，得到所有结果组成的数组，如
	 * all_row("SELECT * FROM `user`");将获得诸如：
	 * [
	 * 		[
	 * 			'Bill',
	 * 			30
	 * 		],
	 * 		[
	 * 			'Hilt',
	 * 			29
	 * 		]
	 * ]
	 * @param  String $sql SQL语句
	 * @return array()
	 */
	public static function all_row($sql){
		return self::fetch_all_row(self::query($sql));
	}
	/**
	 * 查询所有行的单个值，如
	 * all_one("SELECT `name` FROM `user`");将获得诸如：
	 * ['Bill', 'Hilt']
	 * @param  String $sql SQL语句
	 * @return array()
	 */
	public static function all_one($sql){
		return self::fetch_all_one(self::query($sql));
	}
	
	/**
	 * 查询并以指定关键字作为数组的key返回
	 * @param  String $sql SQL语句
	 * @param  String $key 键
	 * @return array()
	 */
	public static function map($sql, $key){
		return self::fetch_map(self::query($sql), $key);
	}
	
	/**
	 * 执行语句，返回结果
	 * @param  String $sql SQL语句
	 * @return result
	 */
	public static function query($command){
        if(self::$connection == null){
            self::connect();
        }
		if($rs = mysqli_query(self::$connection, $command)){
			return $rs;
		}
		IO::e(-1, '数据库查询失败：('.$command.')');
	}
	/**
	 * 查询最后插入的ID
	 * @param  String $sql SQL语句
	 * @return int ID
	 */
	public static function id($id = 'id'){
		$rs = self::query('SELECT LAST_INSERT_ID()');
		if($r = mysqli_fetch_row($rs)){
			return $r[0];
		}
		IO::e(-1, '数据库插入ID查询失败');
	}
	
	
	public static function assoc($sql){
		return mysqli_fetch_assoc(self::query($sql));
	}
	public static function row($sql){
		return mysqli_fetch_row(self::query($rs));
	}
	public static function one($sql){
		$r = mysqli_fetch_row(self::query($sql));
		return empty($r[0]) ? null : $r[0];
	}
	public static function columns($db, $table){
		return self::all_one(
			"SELECT `column_name` 
			 FROM `information_schema`.`columns` 
			 WHERE table_schema='$db' 
			 AND table_name='$table'"
		);
	}

	public static function connect(){
		if($con = mysqli_connect(SQL_SVR, SQL_USR, SQL_PWD, SQL_DB)){
            self::$connection = $con;
			self::set_utf8();
			return $con; 
		}
		IO::e(-1, '数据库连接出错！');
	}
	public static function set_utf8(){
		if(mysqli_query(self::$connection, 'SET NAMES \'utf8\'')){
			return;
		}
		IO::e(-1, '数据库字符集设置失败！');
	}

	public static function fetch_all($rs){
		$a = array();
		while($r = mysqli_fetch_assoc($rs)){
			$a[] = $r;
		}
		return $a;
	}
	public static function fetch_all_row($rs){
		$a = array();
		while($r = mysqli_fetch_row($rs)){
			$a[] = $r;
		}
		return $a;
	}
	public static function fetch_all_one($rs){
		$a = array();
		while($r = mysqli_fetch_row($rs)){
			if(!empty($r[0])){
				$a[] = $r[0];
			}
		}
		return $a;
	}
	public static function fetch_map($rs, $key){
		$a = array();
		while($r = mysqli_fetch_assoc($rs)){
			$a[$r[$key]] = $r;
		}
		return $a;
	}

	public static function group_up_keys($keys){
		$n = count($keys);
		if($n < 1){
			return '';
		}
		$str = "`{$keys[0]}`";
		for($i = 1; $i < $n; $i ++){
			$str .= ",`{$keys[$i]}`";
		}
		return $str;
	}
	public static function group_up_vals($vals){
		$n = count($vals);
		if($n < 1){
			return '';
		}
		$str = "'{$vals[0]}'";
		for($i = 1; $i < $n; $i ++){
			$str .= ",'{$vals[$i]}'";
		}
		return $str;
	}
    public static function group_up_kvs($ary){
        $str = '';
        $snd = false;
        foreach($ary as $k => $v){
            if($snd){
                $str .= ',';
            }
            $str .= "`$k`='$v'";
            $snd = true;
        }
        return $str;
    }
	public static function group_up_where($vals, $cp = 'AND', $op = '='){
		if(is_array($vals)){
			return self::group_up_where_array($vals, $cp = 'AND', $op = '=');
		}else{
			return $vals;
		}
	}
	public static function group_up_where_array($vals, $cp = 'AND', $op = '='){
		$n = count($vals);
		if($n < 1){
			return '';
		}
		$opc = $op;
		$key = '';
		$val = '';
		if(empty($vals[0][2])){
			$key = $vals[0][0];
			$val = $vals[0][1];
		}else{
			$key = $vals[0][0];
			$opc = $vals[0][1];
			$val = $vals[0][2];
		}
		$str = "`$key`$opc'$val'";
		for($i = 1; $i < $n; $i ++){
			if(empty($vals[$i][2])){
				$key = $vals[$i][0];
				$val = $vals[$i][1];
				$opc = $op;
			}else{
				$key = $vals[$i][0];
				$opc = $vals[$i][1];
				$val = $vals[$i][2];
			}
			$str .= "$cp `$key`$opc'$val'";
			
		}
		return $str;
	}

	public static function group_up_pairs($pairs){
		$n = count($pairs);
		if($n < 1){
			return '';
		}
		$str = "`{$pairs[0][0]}`='{$pairs[0][1]}'";
		for($i = 1; $i < $n; $i ++){
			$str .= ",`{$pairs[$i][0]}`='{$pairs[$i][1]}'";
		}
		return $str;
	}
    
    public static function group_up_insert_sql($map, $table){
        $key_ary = array();
        $val_ary = array();
        foreach($map as $k => $v){
            $key_ary[] = $k;
            $val_ary[] = $v;
        }
        $key_str = self::group_up_keys($key_ary);
        $val_str = self::group_up_vals($val_ary);
        return "INSERT INTO `$table` ($key_str) VALUES ($val_str)";
    }
    
    public static function group_up_update_sql($map, $table, $condition = '1'){
        $kvs = self::group_up_kvs($map);
        return "UPDATE `$table` SET $kvs WHERE $condition";
    }

}

